﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using stwh_Common.DBUtility;//Please add references
namespace stwh_DAL
{
    /// <summary>
    /// 数据访问类:stwh_userinfo
    /// </summary>
    public partial class stwh_userinfoDAL : BaseDAL
    {
        public stwh_userinfoDAL()
        { }
        #region  BasicMethod
        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（不带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from view_role_userinfo");
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("view_role_userinfo", "stwh_uiid", FieldColumn, FieldOrder, "stwh_uiid,stwh_rid,stwh_uictime,stwh_uidescription,stwh_uilname,stwh_uiname,stwh_uiportrait,stwh_uipwd,stwh_uistatus,stwh_rctime,stwh_rdelstate,stwh_rdescription,stwh_rname,stwh_rstate", If, pageSize, pageNumber, ref selectCount);
        }

        /// <summary>
        /// 分页获取文本消息
        /// </summary>
        /// <param name="FieldColumn">排序的列名</param>
        /// <param name="FieldOrder">降序排列还是升序排列</param>
        /// <param name="If">查询条件</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageNumber">页码</param>
        /// <param name="selectCount">查询的总记录条数</param>
        /// <param name="d_peopleCount">总记录条数（带条件）</param>
        /// <returns></returns>
        public DataSet GetListByPage(string FieldColumn, string FieldOrder, string If, int pageSize, int pageNumber, ref int selectCount, ref int d_peopleCount, int flag)
        {
            object obj = DbHelperSQL.GetSingle("select count(1) from view_role_userinfo where " + If);
            d_peopleCount = obj != null ? int.Parse(obj.ToString()) : 0;
            return DbHelperSQL.PageData("view_role_userinfo", "stwh_uiid", FieldColumn, FieldOrder, "stwh_uiid,stwh_rid,stwh_uictime,stwh_uidescription,stwh_uilname,stwh_uiname,stwh_uiportrait,stwh_uipwd,stwh_uistatus,stwh_rctime,stwh_rdelstate,stwh_rdescription,stwh_rname,stwh_rstate", If, pageSize, pageNumber, ref selectCount);
        }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("stwh_uiid", "stwh_userinfo");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int stwh_uiid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from stwh_userinfo");
            strSql.Append(" where stwh_uiid=@stwh_uiid");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_uiid", SqlDbType.Int,4)
			};
            parameters[0].Value = stwh_uiid;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(stwh_Model.stwh_userinfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("if (select count(1) from stwh_userinfo where stwh_uilname = '"+model.stwh_uilname+"') = 0 begin ");
            strSql.Append("insert into stwh_userinfo(");
            strSql.Append("stwh_rid,stwh_uiname,stwh_uilname,stwh_uipwd,stwh_uistatus,stwh_uictime,stwh_uiportrait,stwh_uidescription)");
            strSql.Append(" values (");
            strSql.Append("@stwh_rid,@stwh_uiname,@stwh_uilname,@stwh_uipwd,@stwh_uistatus,@stwh_uictime,@stwh_uiportrait,@stwh_uidescription)");
            strSql.Append(";select @@IDENTITY");
            strSql.Append(" end");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rid", SqlDbType.Int,4),
					new SqlParameter("@stwh_uiname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uilname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uipwd", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uistatus", SqlDbType.Int,4),
					new SqlParameter("@stwh_uictime", SqlDbType.DateTime),
					new SqlParameter("@stwh_uiportrait", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uidescription", SqlDbType.NVarChar,300)};
            parameters[0].Value = model.stwh_rid;
            parameters[1].Value = model.stwh_uiname;
            parameters[2].Value = model.stwh_uilname;
            parameters[3].Value = model.stwh_uipwd;
            parameters[4].Value = model.stwh_uistatus;
            parameters[5].Value = model.stwh_uictime;
            parameters[6].Value = model.stwh_uiportrait;
            parameters[7].Value = model.stwh_uidescription;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(stwh_Model.stwh_userinfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("if (select count(1) from stwh_userinfo where stwh_uilname = '" + model.stwh_uilname + "' and stwh_uiid <> " + model.stwh_uiid + ") = 0 begin ");
            strSql.Append("update stwh_userinfo set ");
            strSql.Append("stwh_rid=@stwh_rid,");
            strSql.Append("stwh_uiname=@stwh_uiname,");
            strSql.Append("stwh_uilname=@stwh_uilname,");
            strSql.Append("stwh_uipwd=@stwh_uipwd,");
            strSql.Append("stwh_uistatus=@stwh_uistatus,");
            strSql.Append("stwh_uictime=@stwh_uictime,");
            strSql.Append("stwh_uiportrait=@stwh_uiportrait,");
            strSql.Append("stwh_uidescription=@stwh_uidescription");
            strSql.Append(" where stwh_uiid=@stwh_uiid");
            strSql.Append(" end");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_rid", SqlDbType.Int,4),
					new SqlParameter("@stwh_uiname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uilname", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uipwd", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uistatus", SqlDbType.Int,4),
					new SqlParameter("@stwh_uictime", SqlDbType.DateTime),
					new SqlParameter("@stwh_uiportrait", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uidescription", SqlDbType.NVarChar,300),
					new SqlParameter("@stwh_uiid", SqlDbType.Int,4)};
            parameters[0].Value = model.stwh_rid;
            parameters[1].Value = model.stwh_uiname;
            parameters[2].Value = model.stwh_uilname;
            parameters[3].Value = model.stwh_uipwd;
            parameters[4].Value = model.stwh_uistatus;
            parameters[5].Value = model.stwh_uictime;
            parameters[6].Value = model.stwh_uiportrait;
            parameters[7].Value = model.stwh_uidescription;
            parameters[8].Value = model.stwh_uiid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int stwh_uiid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from stwh_userinfo ");
            strSql.Append(" where stwh_uiid=@stwh_uiid");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_uiid", SqlDbType.Int,4)
			};
            parameters[0].Value = stwh_uiid;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string stwh_uiidlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from stwh_userinfo ");
            strSql.Append(" where stwh_uiid in (" + stwh_uiidlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public stwh_Model.stwh_userinfo GetModel(int stwh_uiid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 stwh_uiid,stwh_rid,stwh_uiname,stwh_uilname,stwh_uipwd,stwh_uistatus,stwh_uictime,stwh_uiportrait,stwh_uidescription,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate from view_role_userinfo ");
            strSql.Append(" where stwh_uiid=@stwh_uiid");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_uiid", SqlDbType.Int,4)
			};
            parameters[0].Value = stwh_uiid;

            stwh_Model.stwh_userinfo model = new stwh_Model.stwh_userinfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="stwh_uilname">登录名</param>
        /// <param name="stwh_uipwd">登录密码</param>
        /// <returns></returns>
        public stwh_Model.stwh_userinfo GetModel(string stwh_uilname, string stwh_uipwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 stwh_uiid,stwh_rid,stwh_uiname,stwh_uilname,stwh_uipwd,stwh_uistatus,stwh_uictime,stwh_uiportrait,stwh_uidescription,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate from view_role_userinfo ");
            strSql.Append(" where stwh_uilname=@stwh_uilname and stwh_uipwd = @stwh_uipwd");
            SqlParameter[] parameters = {
					new SqlParameter("@stwh_uilname", SqlDbType.NVarChar,300),
                    new SqlParameter("@stwh_uipwd", SqlDbType.NVarChar,300)
			};
            parameters[0].Value = stwh_uilname;
            parameters[1].Value = stwh_uipwd;

            stwh_Model.stwh_userinfo model = new stwh_Model.stwh_userinfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0) return DataRowToModel(ds.Tables[0].Rows[0]);
            else return null;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public stwh_Model.stwh_userinfo DataRowToModel(DataRow row)
        {
            stwh_Model.stwh_userinfo model = new stwh_Model.stwh_userinfo();
            if (row != null)
            {
                if (row["stwh_uiid"] != null)
                {
                    model.stwh_uiid = int.Parse(row["stwh_uiid"].ToString());
                }
                if (row["stwh_rid"] != null)
                {
                    model.stwh_rid = int.Parse(row["stwh_rid"].ToString());
                }
                if (row["stwh_uiname"] != null)
                {
                    model.stwh_uiname = row["stwh_uiname"].ToString();
                }
                if (row["stwh_uilname"] != null)
                {
                    model.stwh_uilname = row["stwh_uilname"].ToString();
                }
                if (row["stwh_uipwd"] != null)
                {
                    model.stwh_uipwd = row["stwh_uipwd"].ToString();
                }
                if (row["stwh_uistatus"] != null)
                {
                    model.stwh_uistatus = int.Parse(row["stwh_uistatus"].ToString());
                }
                if (row["stwh_uictime"] != null)
                {
                    model.stwh_uictime = DateTime.Parse(row["stwh_uictime"].ToString());
                }
                if (row["stwh_uiportrait"] != null)
                {
                    model.stwh_uiportrait = row["stwh_uiportrait"].ToString();
                }
                if (row["stwh_uidescription"] != null)
                {
                    model.stwh_uidescription = row["stwh_uidescription"].ToString();
                }
                if (row.ItemArray.Length > 9)
                {
                    if (row["stwh_rname"] != null)
                    {
                        model.stwh_rname = row["stwh_rname"].ToString();
                    }
                    if (row["stwh_rdescription"] != null)
                    {
                        model.stwh_rdescription = row["stwh_rdescription"].ToString();
                    }
                    if (row["stwh_rctime"] != null)
                    {
                        model.stwh_rctime = DateTime.Parse(row["stwh_rctime"].ToString());
                    }
                    if (row["stwh_rstate"] != null)
                    {
                        model.stwh_rstate = int.Parse(row["stwh_rstate"].ToString());
                    }
                    if (row["stwh_rdelstate"] != null)
                    {
                        model.stwh_rdelstate = int.Parse(row["stwh_rdelstate"].ToString());
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select stwh_uiid,stwh_rid,stwh_uiname,stwh_uilname,stwh_uipwd,stwh_uistatus,stwh_uictime,stwh_uiportrait,stwh_uidescription,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate ");
            strSql.Append(" FROM view_role_userinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" stwh_uiid,stwh_rid,stwh_uiname,stwh_uilname,stwh_uipwd,stwh_uistatus,stwh_uictime,stwh_uiportrait,stwh_uidescription,stwh_rname,stwh_rdescription,stwh_rctime,stwh_rstate,stwh_rdelstate ");
            strSql.Append(" FROM view_role_userinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM view_role_userinfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.stwh_uiid desc");
            }
            strSql.Append(")AS Row, T.*  from view_role_userinfo T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  BasicMethod
    }
}

